var pool = require('../database/mysql');




exports.getTradList = function(standDt, gb ,callback){
	
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {
		
		var query = "";
		
		if (gb == "org") {
		
			query = ' select \n ' +
					' l.stock_cd, l.stock_nm, format(l.closing_price,0) as price, \n ' + 
					' format(l.buy_volum,0) as buy, format(l.sell_volum,0) as sell, format(l.purcha_volum,0) as purcha,  \n' +
					' format(l.buy_volum_price,0) as buy_price, l.stock_date  \n' +
					' from tbl_stock_list l \n ' + 
					' where l.stock_date = '+ connection.escape(standDt) + ' \n' +
					' and l.rs_type = ' + connection.escape(gb) + ' \n' +
					' order by l.purcha_volum desc';
	
		} else if(gb == "fore") {
			
			query = ' select \n ' +
					' l.stock_cd, l.stock_nm, format(l.closing_price,0) as closing_price, \n ' + 
					' format(l.avg_trading_volum,0) as avg_tv,  \n' +
					' format(l.purcha_volum,0) as purcha_volum, l.stock_date  \n' +
					' from tbl_stock_list l \n ' + 
					' where l.stock_date = '+ connection.escape(standDt) + ' \n' +
					' and l.rs_type = ' + connection.escape(gb) + ' \n' +
					' order by l.purcha_volum desc';
			
		} else {
			
			query = ' select \n ' +
					' l.stock_cd, l.stock_nm, format(l.closing_price,0) as closing_price, \n ' + 
					' format(l.avg_trading_volum,0) as avg_tv,  \n' +
					' format(l.trading_volum,0) as trading_volum, l.stock_date  \n' +
					' from tbl_stock_list l \n ' + 
					' where l.stock_date = '+ connection.escape(standDt) + ' \n' +
					' and l.rs_type = ' + connection.escape(gb) + ' \n' +
					' order by l.trading_volum desc';
			
		}
	
		console.log(query);
		
		// Use the connection
		connection.query(query, function(err, rows) {
		// 응답합니다.
			callback(err, rows);
			
		});
		connection.release();
	});
};

exports.getInvestor = function(fromDt, endDt, stock_cd, callback){
	
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {
		
		var query = 'select stock_cd, stock_nm, investor_type, \n' +  
					'	case investor_type  \n' +
					'		when \'A002001\' THEN \'금융투자\' \n' +  
					'		when \'A002002\' THEN \'보험\' \n' +
					'	    when \'A002003\' THEN \'투신\'  \n' +
					'		when \'A002004\' THEN \'사모\'  \n' +
					'	    when \'A002005\' THEN \'은행\'  \n' +
					'		when \'A002006\' THEN \'연기금\'  \n' +
					'	    when \'A002000\' THEN \'기관합계\'   \n' +
					'		when \'A001000\' THEN \'개인\'    \n' +
					'	    when \'A003000\' THEN \'외국인\'  \n' +
					'	    when \'A003001\' THEN \'기타외국인\'  \n' +
					'	    when \'A002007\' THEN \'기타금융\'  \n' +
					'	    when \'A002008\' THEN \'국가\'  \n' +
					'	    when \'A002009\' THEN \'기타법인\'  \n' +
					'	  end as investor,   \n' + 
		 ' format(sum(buy_volum), 0) as buy_volum, format(sum(sell_volum),0) as sell_volum, \n' + 
		 ' format(sum(purcha_volum),0) as  purcha_volum from tbl_stock_investor  \n' +
		 ' where stock_date between ' + connection.escape(fromDt) + 'and ' + connection.escape(endDt) +  '\n' +
		 ' and stock_cd =  ' +  connection.escape(stock_cd) +   '\n' +
		 ' and investor_type <> \'A002000\' ' +
		 ' group by stock_cd, stock_nm, investor, investor_type'
		 
		 ;
		
		
		console.log(query);
		
		// Use the connection
		connection.query(query, function(err, rows) {
		// 응답합니다.
			
			callback(err, rows);
			
		});
		connection.release();
	});
};

exports.getStockInfo = function(stock_cd, stock_date, callback){
	
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {
		
		var query = 'select stock_nm, stock_cd, format(closing_price, 0) as closing_price,  \n' + 
					' fluctuation_rate, format(trading_volum, 0) as trading_volum, \n' + 
					' CASE length(substr(contrast,3) ) when  0  then \'0\'  ELSE  substr(contrast,3) END  as contvalue, \n' +
					' CASE substr(contrast,1,2)  \n' + 
					' when  \'상한\'  then \'2\'  \n' + 
					' when  \'상승\'  then \'1\'   \n' + 
					' when  \'하락\'  then \'-1\'   \n' + 
					' when  \'하한\'  then \'-2\'   \n' + 
					' ELSE  \'0\' END  as contrast, \n' + 
					' format(listed_stocks,0) as  listed_stocks from tbl_stock_price  \n' + 
		 			' where stock_cd = ' +  connection.escape(stock_cd) +   '\n' +
		 			' and stock_date =  ' +  connection.escape(stock_date) +   '\n' ;
		
		console.log(query);
		
		// Use the connection
		connection.query(query, function(err, rows) {
		// 응답합니다.
		
			callback(err, rows);
			
		});
		connection.release();
	});
};



exports.getStockInfo1 = function(stock_cd, callback){
	
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {
		
		var query = 'select stock_date ,stock_nm, stock_cd, format(closing_price, 0) as closing_price,  \n' + 
					' fluctuation_rate, format(trading_volum, 0) as trading_volum, \n' + 
					' CASE length(substr(contrast,3) ) when  0  then \'0\'  ELSE  substr(contrast,3) END  as contvalue, \n' +
					' CASE substr(contrast,1,2)  \n' + 
					' when  \'상한\'  then \'2\'  \n' + 
					' when  \'상승\'  then \'1\'   \n' + 
					' when  \'하락\'  then \'-1\'   \n' + 
					' when  \'하한\'  then \'-2\'   \n' + 
					' ELSE  \'0\' END  as contrast, \n' + 
					' format(listed_stocks,0) as  listed_stocks from tbl_stock_price  \n' + 
		 			' where stock_cd = ' +  connection.escape(stock_cd) +   '\n' +
		 			' and stock_date = (select max(stock_date) from tbl_stock_price where stock_cd =' + connection.escape(stock_cd) +  ')\n' ;
		
		
		// Use the connection
		connection.query(query, function(err, rows) {
		// 응답합니다.
			callback(err, rows);
			
		});
		connection.release();
	});
};

exports.getStockName = function(stock_nm, callback){
	
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {
		
		var query = 'select stock_date, stock_cd, stock_nm, format(closing_price, 0) as closing_price,   \n' + 
		' CASE length(substr(contrast,3) ) when  0  then \'0\'  ELSE  substr(contrast,3) END  as contvalue, \n' +
		' CASE substr(contrast,1,2)  \n' + 
		' when  \'상한\'  then \'2\'  \n' + 
		' when  \'상승\'  then \'1\'   \n' + 
		' when  \'하락\'  then \'-1\'   \n' + 
		' when  \'하한\'  then \'-2\'   \n' + 
		' ELSE  \'0\' END  as contrast, \n' + 
		' fluctuation_rate, format(trading_volum,0) as trading_volum from tbl_stock_price \n' + 
		' where stock_date = (select max(stock_date) from tbl_stock_price) \n' +
		' and stock_nm like ' +  connection.escape('%'+stock_nm +'%') +   '      \n' ;
		// Use the connection
		
		
		console.log(query);
		connection.query(query, function(err, rows) {
			// 응답합니다.
			callback(err, rows);
			
		});
		connection.release();
	});
};

exports.getSourceList = function(stock_cd, callback){
	
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {
		
		var query = ' select stock_date, \n' +  
					' sum(case investor_type when \'A001000\' THEN purcha_volum else 0 end) as a100,  \n' +
					' sum(case investor_type when \'A002001\' THEN purcha_volum else 0 end) as a201,  \n' +
					' sum(case investor_type when \'A002002\' THEN purcha_volum else 0 end) as a202,  \n' +
					' sum(case investor_type when \'A002003\' THEN purcha_volum else 0 end) as a203,  \n' +
					' sum(case investor_type when \'A002004\' THEN purcha_volum else 0 end) as a204,  \n' +
					' sum(case investor_type when \'A002005\' THEN purcha_volum else 0 end) as a205,  \n' +
					' sum(case investor_type when \'A002006\' THEN purcha_volum else 0 end) as a206,  \n' +
					' sum(case investor_type when \'A002007\' THEN purcha_volum else 0 end) as a207,  \n' +
					' sum(case investor_type when \'A002008\' THEN purcha_volum else 0 end) as a208,  \n' +
					' sum(case investor_type when \'A002009\' THEN purcha_volum else 0 end) as a209,  \n' +
					' sum(case investor_type when \'A003000\' THEN purcha_volum else 0 end) as a300,  \n' +
					' sum(case investor_type when \'A003001\' THEN purcha_volum else 0 end) as a301  \n' +
					' from tbl_stock_supply_demand \n ' +
					' where stock_cd =  ' +  connection.escape(stock_cd) +
					'group by stock_date order by stock_date asc';
		
		console.log(query);
		
		// Use the connection
		connection.query(query, function(err, rows) {
		// 응답합니다.
			callback(err, rows);
			
		});
		connection.release();
	});
};


exports.getAgentSourceList = function(stock_cd, agent, callback){
	
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {
		
		var query = 'select stock_date, purcha_volum as a100, price \n' +  
		' from tbl_stock_supply_demand  \n' +
		' where stock_cd = ' +  connection.escape(stock_cd) +  ' \n' +
		' and investor_type = ' +  connection.escape(agent) +  ' \n' +
		' order by stock_date asc';
		
		console.log(query);
		
		// Use the connection
		connection.query(query, function(err, rows) {
		// 응답합니다.
			callback(err, rows);
			
		});
		connection.release();
	});
};


exports.getUserInfo = function(user_id, callback){
	
	pool.getConnection(function(err, connection) {

		  // Use the connection
		connection.query( 'SELECT * FROM tbl_stock_user WHERE user_id = ?', [user_id], function(err, rows) {
			// 응답합니다.
			callback(err, rows);
		});
		connection.release();
	});
	
};


exports.del = function(id) {
	//데이타베이스 쿼리를 실행합니다.
	pool.getConnection(function(err, connection) {

		// Use the connection
		connection.query( 'DELETE FROM products WHERE id = ? ', [id]) ; 
		connection.release();

	});
};


exports.insert = function(req) {
	pool.getConnection(function(err, connection) {
		// Use the connection
		connection.query( 'INSERT INTO products(name, modelnumber, series) VALUES ( ?,?,?)', [req.param('name'),req.param('modelnumber'),req.param('series')]) ; 
		connection.release();
	});
};

exports.editForm = function(id, callback) {
	pool.getConnection(function(err, connection) {

		  // Use the connection
		connection.query( 'SELECT * FROM products WHERE id = ?', [id], function(err, rows) {
			// 응답합니다.
			callback(err, rows[0]);
			connection.release();
		});
	});
};

exports.edit = function(req) {
	// Use the connection
	pool.getConnection(function(err, connection) {

		// Use the connection
		connection.query( 'UPDATE products SET name = ?, modelnumber = ?, series = ? WHERE id = ?', [req.param('name'), req.param('modelnumber'), req.param('series'),req.param('id')]) ; 
		connection.release();
	});
};

